/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apirose;

import apirose.APIEdT;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author v-tech_master
 */
public class Edt_MySQL extends BD_MySQL implements APIEdT{
    
    Request_MySQL bdd;
    int idPromotion;

    Edt_MySQL(Request_MySQL request, int idPromo) {
        this.bdd = request;
        this.idPromotion = idPromo;
    }

    @Override
    public Date getDebutPlageHoraireBDD(int idCours) {
        Date debutCreneau = null;
        
        String req = "SELECT tsDebut FROM Cours WHERE id = "+idCours+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                Timestamp dateamps = rs.getTimestamp("tsDebut");
                debutCreneau = new Date(dateamps.getTime());
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return debutCreneau;
    }

    @Override
    public Date getFinPlageHoraireBDD(int idCours) {
        Date finCreneau = null;
        
        String req = "SELECT tsFin FROM Cours WHERE id = "+idCours+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                Timestamp dateamps = rs.getTimestamp("tsFin");
                finCreneau = new Date(dateamps.getTime());
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return finCreneau;
    }

    @Override
    public String getTypeCoursPlageHoraireBDD(int idCours) {
        String type = null;
        
        String req = "SELECT nom FROM Cours, Type_Cours WHERE Cours.id = "+idCours+" AND Cours.idTypeCours = Type_Cours.id;";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                type = rs.getString("nom");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return type;
    }

    @Override
    public int savePlageHoraireBDD(Date deb, Date fin, int idSeance) {
        Integer id = null;
        Timestamp tsDebut = new Timestamp(deb.getTime());
        Timestamp tsFin = new Timestamp(fin.getTime());
        
        String req = "SELECT idIntervenant, idUE, idSalle, idTypeCours FROM Seance WHERE id="+idSeance+";";

        ResultSet rs = bdd.executer_requete(req);

        try {
            rs.next();
            int idIntervenant = rs.getInt("idIntervenant");
            int idUE = rs.getInt("idUE");
            int idSalle = rs.getInt("idSalle");
            int idType = rs.getInt("idTypeCours");

            req = "INSERT INTO Cours (idSalle, idIntervenant, idUE, idTypeCours, tsDebut, tsFin) "
                    +"VALUES ("+idSalle+", "+idIntervenant+", "+idUE+", "+idType+", '"+tsDebut+"', '"+tsFin+"');";
            id = bdd.executer_update(req);
            
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }

        return id;
        
    }
    
    @Override
    public void updatePlageHoraireBDD(int idPlageHoraire, Date deb, Date fin) {
        Timestamp tsDebut = new Timestamp(deb.getTime());
        Timestamp tsFin = new Timestamp(fin.getTime());
        
        String req = "UPDATE Cours SET tsDebut='"+tsDebut+"', tsFin='"+tsFin+"' WHERE id="+idPlageHoraire+";";
        bdd.executer_update(req);
    }

    @Override
    public Date getDateDebBDD() {
        Date debutSemestre = null;
        
        String req = "SELECT tsDebut FROM Promotion WHERE id = "+this.idPromotion+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                Timestamp dateamps = rs.getTimestamp("tsDebut");
                debutSemestre = new Date(dateamps.getTime());
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return debutSemestre;
    }

    @Override
    public Date getDateFinBDD() {
        Date finSemestre = null;
        
        String req = "SELECT tsFin FROM Promotion WHERE id = "+this.idPromotion+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                Timestamp dateamps = rs.getTimestamp("tsFin");
                finSemestre = new Date(dateamps.getTime());
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return finSemestre;
    }

    @Override
    public ArrayList<Integer> getListeIdCreneauNonExploitableBDD() {
        ArrayList<Integer> listeId = new ArrayList<Integer>();
        
        String req = "SELECT id FROM JourNonOuvrable WHERE idPromotion = "+this.idPromotion+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                listeId.add(rs.getInt("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return listeId;
    }

    @Override
    public ArrayList<Integer> getListIdPlageHoraireBDD() {
        ArrayList<Integer> listeIdUE = new ArrayList<Integer>();
        ArrayList<Integer> listeId = new ArrayList<Integer>();
        
        String req1 = "SELECT id FROM UE WHERE idPromotion = "+this.idPromotion+";";
        
        ResultSet rs1 = bdd.executer_requete(req1);
        try {
            while(rs1.next()){
                listeIdUE.add(rs1.getInt("id"));
            }
            
            String req2;
            ResultSet rs2;
            
            for(int currant : listeIdUE){
                req2 = "SELECT id FROM Cours WHERE idUE = "+currant+";";
        
                rs2 = bdd.executer_requete(req2);
                while(rs2.next()){
                    listeId.add(rs2.getInt("id"));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return listeId;
    }

    @Override
    public void saveContrainteEDT(Date debSem, Date finSem) {
        Timestamp tsDebut = new Timestamp(debSem.getTime());
        Timestamp tsFin = new Timestamp(finSem.getTime());
        
        String req = "UPDATE Promotion SET tsDebut='"+tsDebut+"', tsFin='"+tsFin+"' WHERE id="+this.idPromotion+";";
        
        this.bdd.executer_update(req);
    }

    @Override
    public Date getCreneauExploiteDebBDD(int idJourNonOuvrable) {
       Date date = null;
        
        String req = "SELECT tsDebut FROM JourNonOuvrable WHERE id = "+idJourNonOuvrable+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                Timestamp dateamps = rs.getTimestamp("tsDebut");
                date = new Date(dateamps.getTime());
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return date;
    }

    @Override
    public Date getCreneauExploiteFinBDD(int idJourNonOuvrable) {
        Date date = null;
        
        String req = "SELECT tsFin FROM JourNonOuvrable WHERE id = "+idJourNonOuvrable+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                Timestamp dateamps = rs.getTimestamp("tsFin");
                date = new Date(dateamps.getTime());
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return date;
    }

    @Override
    public String getTypeCreneauNonExploitableBDD(int idJourNonOuvrable) {
        String type = null;
        
        String req = "SELECT type FROM JourNonOuvrable WHERE id = "+idJourNonOuvrable+";";
        
        ResultSet rs = bdd.executer_requete(req);
        try {
            while(rs.next()){
                type = rs.getString("type");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Edt_MySQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return type;
    } 
    
}
